Create Staging Tables in Staging Database and Populate the Staging Tables - Continued4 12

To Load Load_StgSpend Table:





 

Source OLE DB Source Spend Table:




Columns Tab:








Derived Column Empty to NULL



Derived Column Text:

GETDATE()

GETDATE()

ISNULL([Main Document ID]) ? "Unknown" : [Main Document ID]

ISNULL([Work Order ID]) ? "Unknown" : [Work Order ID]

0

ROUND([Invoice Amount],2)

REPLACENULL([Line Item Amount (Vendor & by Rate Category) (ST/Hr)],"0")

0

ISNULL([Bill Rate (Monthly/MO)]) ? 0 : [Bill Rate (Monthly/MO)]

ISNULL([Invoice Line Item Amount]) ? 0 : [Invoice Line Item Amount]

ISNULL([Time Sheet Amount (ST/Hr)]) ? 0 : [Time Sheet Amount (ST/Hr)]

ISNULL([Time Sheet Amount (OT/Hr)]) ? 0 : [Time Sheet Amount (OT/Hr)]

ISNULL([Credit/Debit Memo Amount (DT/Hr)]) ? 0 : [Credit/Debit Memo Amount (DT/Hr)]

ISNULL([Credit/Debit Memo Amount (OT/Hr)]) ? 0 : [Credit/Debit Memo Amount (OT/Hr)]

ISNULL([Credit/Debit Memo Amount (DT/Hr)]) ? 0 : [Credit/Debit Memo Amount (DT/Hr)]

ISNULL([Tax Amount]) ? 0 : [Tax Amount]

ISNULL([Bill Rate (OT/Hr)]) ? 0 : [Bill Rate (OT/Hr)]

ISNULL([Bill Rate (DT/Hr)]) ? 0 : [Bill Rate (DT/Hr)]

ISNULL([MSP Amount]) ? 0 : [MSP Amount]

ISNULL([Credit/Debit Memo Amount (ST/Hr)]) ? 0 : [Credit/Debit Memo Amount (ST/Hr)]

ISNULL([Line Item Amount (Vendor & by Rate Category) (OT/Hr)]) ? 0 : [Line Item Amount (Vendor & by Rate Category) (OT/Hr)]

ISNULL([Line Item Amount (Vendor & by Rate Category) (DT/Hr)]) ? 0 : [Line Item Amount (Vendor & by Rate Category) (DT/Hr)]

ISNULL([Line Item Amount (Vendor & by Rate Category) (Monthly/MO)]) ? 0 : [Line Item Amount (Vendor & by Rate Category) (Monthly/MO)]

ISNULL([Billable Hours (Monthly/MO)]) ? 0 : [Billable Hours (Monthly/MO)]

ISNULL([Time Sheet Amount (Monthly/MO)]) ? 0 : [Time Sheet Amount (Monthly/MO)]

ISNULL([Credit/Debit Memo Amount (Monthly/MO)]) ? 0 : [Credit/Debit Memo Amount (Monthly/MO)]


Data Conversion:



OLE DB Destination:


Mappings Tab:







To Load_StgInvoice Table:







Source Alt_Spend:




Column Tab:





Data Conversions:



Derived Columns:




 

Derived Columns Text:

 

GETDATE()

GETDATE()

ISNULL([Job Seeker ID]) ? "Unkown" : [Job Seeker ID]

ISNULL([Diverse Supplier Filter 1]) ? "Unkown" : [Diverse Supplier Filter 1]

ISNULL([Main Document ID]) ? "Unknown" : [Main Document ID]

ISNULL([Work Order ID]) ? "Unknown" : [Work Order ID]

ISNULL([Invoice ID]) ? "Unknown" : [Invoice ID]

ISNULL([Consolidated Invoice ID]) ? "Unknown" : [Consolidated Invoice ID]

ISNULL([Invoice Type]) ? "Unknown" : [Invoice Type]

ISNULL([Invoice Line Item Type]) ? "Unknown" : [Invoice Line Item Type]

ISNULL([Invoice Line Item ID]) ? "Unknown" : [Invoice Line Item ID]

ISNULL([Worker Type]) ? "Unknown" : [Worker Type]

ISNULL(Currency) ? "Unknown" : Currency

ISNULL([Diverse?]) ? "Unknown" : [Diverse?]

ISNULL([Tier 1 Supplier?]) ? "Unknown" : [Tier 1 Supplier?]

ISNULL([Copy of Consolidated Invoice End Date]) ? "Unknown" : [Copy of Consolidated Invoice End Date]

ISNULL([Copy of Invoice Line Item Date]) ? "Unknown" : [Copy of Invoice Line Item Date]

ISNULL([Copy of Invoice Line Item Start Date]) ? "Unknown" : [Copy of Invoice Line Item Start Date]

ISNULL([Copy of Invoice Approved Date]) ? "Unknown" : [Copy of Invoice Approved Date]

ISNULL([Copy of Time Sheet Submit Date]) ? "Unknown" : [Copy of Time Sheet Submit Date]

ISNULL([Copy of Expense Sheet Approved Date]) ? "Unknown" : [Copy of Expense Sheet Approved Date]

ISNULL([Copy of Time Sheet Approved Date]) ? "Unknown" : [Copy of Time Sheet Approved Date]

 

  

Conditional Split:




Derived Column 1:




 

Union All:






OLE DB Destination




Mappings Tab:






Multicast Transformation

https://www.sqlshack.com/ssis-multicast-transformation-overview/